<?php
App::uses('AppController', 'Controller');
App::uses('Sanitize', 'Utility');

App::import('Controller', 'Colonnes');
/**
 * Tables Controller
 *
 * @property Table $Table
 */
class TablesController extends AppController {

	//var $components = array('Maconnexion', 'Session');

	public $helpers = array('Date', 'Datefr', 'Moninput', 'Session_gpp');
/**
 * RLEB
 *
 *
 * @return void
 */
	public function TablesOfVersion($id) {
		$id = Sanitize::paranoid($id);
		$this->Table->recursive = 0;
		//$data = $this->Table->find('first',array('conditions' => array("Table.version_id <="=>$id),'recursive'=>-1));
    	$saversion=$this->Table->Version->find('first',array('conditions' => array('Version.id' => $id),'recursive'=>-1));
    	$sonapp = $this->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));

		$this->paginate = 
			array(
				'conditions' => array(
					"Table.version_id in (select ver.id
		            					from tables tab, versions ver
		                				where tab.version_id=ver.id
		            					and ver.application_id=".$sonapp['Application']['id']."
		                         		)",
		         	"not exists (select 1 
		            			from tables tab , versions ver
		            			where tab.version_id=ver.id
		 						and ver.application_id=1
		            			and tab.name=`table`.name
		            			and tab.version_id>`table`.version_id
		            			and tab.version_id<=".$id."
		            			)",
						"(Table.version_id_fin = 0 or Table.version_id_fin > ".$id.")",
						"Table.version_id <= ".$id
	         	), 
	        //'limit' => 25,
	        'order' => array('Table.name' => 'asc') 
	    );

		$data = $this->paginate('Table',array("Table.version_id <="=>$id));
    	
    	foreach ($data as $table):
			$nbcolonne = $this->Table->Colonne->find('count', array('fields' => 'Colonne.id', 'conditions' => array('Colonne.table_id =' => $table['Table']['id']),'recursive'=>-1 ));
			$nbcolonnes[$table['Table']['id']]=array("count"=>$nbcolonne);

			try{
		        $reponse = $this->Table->query('SELECT count(*) as nblignes FROM Gestparamphp_$'.$sonapp['Application']['id'].'.'.$table['Table']['name'].'_$'.$table['Table']['id']);
		        //debug($reponse);
		        foreach ($reponse[0] as $donnees ) {
			        $nblignes[$table['Table']['id']]=array("count"=>$donnees['nblignes']);
			    }
			}catch (Exception $e){
				$nblignes[$table['Table']['id']]=array("count"=>"-1");
			} 
			//$nbligne = $this->Table->Ligne->find('count', array('fields' => 'Ligne.id', 'conditions' => array('Ligne.table_name =' => $table['Table']['name']),'recursive'=>-1 ));
			//$nblignes[$table['Table']['id']]=array("count"=>$nbligne);
		endforeach;

		if (isset($nbcolonnes)){
			$this->set('nbcolonnes',$nbcolonnes);
		}	
		if (isset($nblignes)){
			$this->set('nblignes',$nblignes);
		}		
		$this->set('tables', $data);
		$this->set('saversion', $saversion);
		$this->set('sonapp', $sonapp);
		
		$this->render("index");
	}

/**
 * view method
 *
 * @param string $id
 * @return void
 */
	public function view($id, $version) {
		$id = Sanitize::paranoid($id);
		$version = Sanitize::paranoid($version);
		$this->Table->id = $id;
		if (!$this->Table->exists()) {
			throw new NotFoundException(__('Invalid table'));
		}
		$versionencours = $this->Table->Version->find('first',array('conditions' => array('Version.id' => $version),'recursive'=>-1));
		
		$unetable = $this->Table->find('first',array('conditions' => array('Table.id' => $id),'recursive'=>-1));
		$saversion = $this->Table->Version->find('first',array('conditions' => array('Version.id' => $unetable['Table']['version_id']),'recursive'=>-1));
		$sonapp = $this->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));
		$this->set('saversion', $saversion);
		$this->set('sonapp', $sonapp);

		$data=$this->Table->read(null, $id);
		//$data = $this->Table->find('all', array( 'conditions' => array('Table.id =' => $id),'order'=>array('topPk desc, id asc'), 'recursive'=>1 ));
		
		$colonnes = $this->Table->Colonne->find('all', array( 'conditions' => array('Colonne.table_id =' => $id),'order'=>array('topPk desc, id asc'), 'recursive'=>-1 ));
		//$sapk = $this->Table->Colonne->find('all', array('conditions' => array('Colonne.table_id =' => $id, 'Colonne.topPk =' => '1'),'recursive'=>-1 ));
		//$data['Colonne']=$colonnes;
		$i=0;
		foreach ($colonnes as $colonne): 
			$colonne=$colonne['Colonne'];
			$data['Colonne'][$i]=$colonne;
			$pgtypecol_name = $this->Table->Colonne->Pgtypecol->find('first',array('conditions' => array('id' => $colonne['pgtypecol_id']),'recursive'=>-1));
			$data['Colonne'][$i]['pgtypecol_name'] = $pgtypecol_name['Pgtypecol']['name'];
			$i=$i+1;
		endforeach;
		$this->set('table', $data);
		$this->set('versionencours', $versionencours);
	}


	public function import($version_id) {
		$version_id = Sanitize::paranoid($version_id);
		$saversion = $this->Table->Version->find('first',array('conditions' => array('Version.id' => $version_id),'recursive'=>-1));
		$sonapp = $this->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));
		
		if ($this->request->is('post')) {
			$retour='OK';
			if(isset($this->request->data['Import'])){
				foreach($this->request->data['Import'] as $key => $value){
					if ($sonapp['Application']['pgdatabase_id']=='1'){
						$req= "SELECT TABLE_SCHEMA AS OWNER, TABLE_NAME AS TABLE_NAME, CONCAT( CONCAT( TABLE_SCHEMA,  '.' ) , TABLE_NAME ) AS TABLE_ID, TABLE_COMMENT AS COMMENTS
								FROM information_schema.tables
								WHERE UPPER( TABLE_SCHEMA ) = UPPER('".$sonapp['Application']['sid']."')
								AND UPPER( TABLE_NAME ) = UPPER('".$value."')
								ORDER BY TABLE_NAME";
					}
					if ($sonapp['Application']['pgdatabase_id']=='2'){
						$req=  "SELECT t.owner AS OWNER, t.TABLE_NAME AS TABLE_NAME, CONCAT(CONCAT(t.owner, '.'), t.TABLE_NAME) AS TABLE_ID, co.COMMENTS AS COMMENTS 
								  FROM sys.ALL_TABLES t, sys.ALL_TAB_COMMENTS co
								 WHERE UPPER(t.owner) = UPPER('".$sonapp['Application']['login']."')
								   AND UPPER(t.table_name) = UPPER('".$value."')
								   AND t.owner = co.owner
								   AND t.table_name = co.table_name
								 ORDER BY t.table_name";
					}

					try{
						$reponse=$this->Maconnexion->dmlquery($sonapp, $req, 1);
					} catch (Exception $e) {
						$i=0;
					}
					if ($reponse!=null){
						$this->request->data['Table']['name']=$reponse[0]['TABLE_NAME'];
						$this->request->data['Table']['version_id']=$version_id;
						$this->request->data['Table']['description']=$reponse[0]['COMMENTS'];
					}

					$table_id=$this->creertable($version_id);
					if ($table_id==null){
						$retour='KO';
					}else{
						$aCol= new ColonnesController;
						$aCol->constructClasses();

						if($aCol->importColonneOfTable($table_id)=='KO'){
							$retour='KO';
						}
					}
				}
				if($retour=='OK'){
					$this->Session->setFlash(__('The tables have been imported successful'),'default',array("class"=>'success'));
				
				}else{
					$this->Session->setFlash(__('An error has occured in the import. Please, try again.'),'default',array("class"=>'error'));
				}
			}
			$this->request=null;
			$this->redirect(array('controller'=>'versions','action' => 'view',$version_id));
		}
		
			
		
		/*$req= "SELECT c.owner, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.DATA_LENGTH, c.DATA_PRECISION, c.COLUMN_ID, c.NULLABLE
				 FROM sys.ALL_TAB_COLS c, sys.ALL_TABLES t
				WHERE t.owner = 'SOC1'
			      AND t.table_name = c.TABLE_NAME
			 ORDER BY t.table_name, c.column_id";*/
		$req='';

		if ($sonapp['Application']['pgdatabase_id']=='1'){
			$req= "SELECT TABLE_SCHEMA AS OWNER, TABLE_NAME AS TABLE_NAME, CONCAT( CONCAT( TABLE_SCHEMA,  '.' ) , TABLE_NAME ) AS TABLE_ID
					 FROM information_schema.tables
					WHERE UPPER(TABLE_SCHEMA) =  UPPER('".$sonapp['Application']['sid']."')
				 ORDER BY TABLE_NAME";
		}
		if ($sonapp['Application']['pgdatabase_id']=='2'){
			$req= "SELECT t.owner as OWNER, t.TABLE_NAME as TABLE_NAME, CONCAT( CONCAT( t.owner,  '.' ) , t.TABLE_NAME ) AS TABLE_ID
  					 FROM sys.ALL_TABLES t
 					WHERE UPPER(t.owner) = UPPER('".$sonapp['Application']['login']."')
 				 ORDER BY t.table_name";
		}

		//if ($req!=''){
			try{
				$reponse=$this->Maconnexion->dmlquery($sonapp, $req, 1);
				$this->set('table_distantes', $reponse);
			} catch (Exception $e) {
				$i=0;
			}
		//}
		
		$this->set('saversion', $saversion);
		$this->set('sonapp', $sonapp);
	}

	private function creertable($idversion) {
		//suppression de caractères spéciaux dans le nom de la table sauf des _ et -
		$this->request->data['Table']['name'] = Sanitize::paranoid($this->request->data['Table']['name'],array('_', '-')); 
		//Mise en majuscule du nom de la table
		$this->request->data['Table']['name'] = strtoupper($this->request->data['Table']['name']);

		//$this->request->data = Sanitize::clean($this->request->data, array('encode' => false, 'unicode'=> true));
		//debug($this->request->data);
		$this->Table->create();
		if ($this->Table->save($this->request->data)) {

			$saversion=$this->Table->Version->find('first',array('conditions' => array('Version.id' => $idversion),'recursive'=>-1));
			$sonapp = $this->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));
			
			//si base local alors on crée la table
			if ($sonapp['Application']['hebergement']==0) {
				//$this->Maconnexion->changer($saversion['Version']['application_id']);
				 $this->Maconnexion->ddlquery($sonapp,
		        	'CREATE TABLE IF NOT EXISTS `'.$this->request->data['Table']['name'].'_$'.$this->Table->id.'` (
					  `Gestparamphp_id` BIGINT NOT NULL AUTO_INCREMENT,
					  `Gestparamphp_VERSION` int(11) NOT NULL,
					  `Gestparamphp_VERSION_fin` int(11) NOT NULL,
					  PRIMARY KEY (`Gestparamphp_id`)
					) ENGINE=MyISAM DEFAULT CHARSET=utf8;');
				//$this->Maconnexion->changer();
			}

			$this->Session->setFlash(__('The table has been saved'),'default',array("class"=>'success'));
			return $this->Table->id;	
		}else{
			$this->Session->setFlash(__('The table could not be saved. Please, try again.'),'default',array("class"=>'error'));
			return null;
		}
	}
/**
 * add method
 *
 * @return void
 */
	public function add($idversion) {
		$idversion = Sanitize::paranoid($idversion);
		//debug($this->request->data);
		if ($this->request->is('post')) {
			if($this->creertable($idversion)!=null){
				$this->redirect(array('controller'=>'versions','action' => 'view',$idversion));
			}
		}
		//$versions = $this->Table->Version->find('list');
		$this->set('idversion',$idversion);
		//$this->set(compact('versions'));
	}

/**
 * edit method
 *
 * @param string $id
 * @return void
 */
	public function edit($id , $idversion) {
		$id = Sanitize::paranoid($id);
		$this->Table->id = $id;
		if (!$this->Table->exists()) {
			throw new NotFoundException(__('Invalid table'));
		}
		if ($this->request->is('post') || $this->request->is('put')) {
			//suppression de caractères spéciaux dans le nom de la table
			$this->request->data['Table']['name'] = Sanitize::paranoid($this->request->data['Table']['name'],array('_', '-')); 
			//Mise en majuscule du nom de la table
			$this->request->data['Table']['name'] = strtoupper($this->request->data['Table']['name']);
			
			//$this->request->data = Sanitize::clean($this->request->data, array('encode' => false, 'unicode'=> true));
			
			$latable = $this->Table->find('first',array('conditions' => array('Table.id' => $id),'recursive'=>-1));
			$saversion = $this->Table->Version->find('first',array('conditions' => array('Version.id' => $latable['Table']['version_id']),'recursive'=>-1));
			$sonapp = $this->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));
			
				
			if ($this->Table->save($this->request->data)) {
				if($latable['Table']['name']!=$this->request->data['Table']['name']){
			        //$this->Maconnexion->changer($saversion['Version']['application_id']);
			         $this->Maconnexion->ddlquery($sonapp,
			        	"RENAME TABLE ".$latable['Table']['name']."_$".$latable['Table']['id']
			        	." to ".$this->request->data['Table']['name']."_$".$latable['Table']['id'].";"
			        );
			        //$this->Maconnexion->changer();
			    }
				$this->Session->setFlash(__('The table has been saved'),'default',array("class"=>'success'));
				$this->redirect(array('action' => 'view',$id, $idversion));
			} else {
				$this->Session->setFlash(__('The table could not be saved. Please, try again.'),'default',array("class"=>'error'));
			}
		} else {
			$this->request->data = $this->Table->read(null, $id);
		}
		//$versions = $this->Table->Version->find('list');
		$this->set(compact('versions'));
	}

/**
 * delete method
 *
 * @param string $id
 * @return void
 */
	public function delete($id, $idversion) {
		$id = Sanitize::paranoid($id);
		$idversion = Sanitize::paranoid($idversion);
		if (!$this->request->is('post')) {
			throw new MethodNotAllowedException();
		}
		$this->Table->id = $id;

		if (!$this->Table->exists()) {
			throw new NotFoundException(__('Invalid table'));
		}

		$latable = $this->Table->find('first',array('conditions' => array('Table.id' => $id),'recursive'=>-1));
		$saversion = $this->Table->Version->find('first',array('conditions' => array('Version.id' => $latable['Table']['version_id']),'recursive'=>-1));
		$sonapp = $this->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));
		
		/*si base local  et la version de la table est différente de la version encours alors on fait une suppression logique grace à version_id_fin
		sinon on fait une suppression physique*/
		if ($sonapp['Application']['hebergement']==0 && $idversion != $saversion['Version']['id']){
			$latable['Table']['version_id_fin'] = $idversion;
			
			$this->Table->save($latable);

			//$this->Maconnexion->changer($saversion['Version']['application_id']);
			 $this->Maconnexion->ddlquery($sonapp,
	        	"UPDATE ".$latable['Table']['name']."_$".$latable['Table']['id']." "
	        	."SET Gestparamphp_VERSION_fin='".$idversion."' "
	        	."where Gestparamphp_VERSION_fin = 0 ;");
			//$this->Maconnexion->changer();
			
			$this->redirect(array('controller'=>'versions','action' => 'view',$idversion));

		}else{
			if ($this->Table->delete()) {
				//si base local alors on crée la table
				if ($sonapp['Application']['hebergement']==0) {
					//$this->Maconnexion->changer($saversion['Version']['application_id']);
					$this->Maconnexion->ddlquery($sonapp, 'DROP TABLE `'.$latable['Table']['name'].'_$'.$latable['Table']['id'].'` ;');
					//$this->Maconnexion->changer();
				}

				$this->Session->setFlash(__('The table has been deleted'),'default',array("class"=>'success'));
				//$this->redirect(array('action' => 'TablesOfVersion',$idversion));
				$this->redirect(array('controller'=>'versions','action' => 'view',$saversion['Version']['id']));
	        }
		}
		$this->Session->setFlash(__('The table could not be saved. Please, try again.'),'default',array("class"=>'error'));
		$this->redirect(array('action' => 'TablesOfVersion',$idversion));
	}

}